/**
 * Copyright (C) 2008 by The Regents of the University of California
 * Redistribution of this file is permitted under the terms of the GNU
 * Public License (GPL).
 *
 * @author Junghoo "John" Cho <cho AT cs.ucla.edu>
 * @date 3/24/2008
 */

#include <cstdio>
#include <iostream>
#include <fstream>
#include "Bruinbase.h"
#include "SqlEngine.h"
#include "BTreeIndex.h"
#include "limits.h"

using namespace std;

// external functions and variables for load file and sql command parsing 
extern FILE* sqlin;
int sqlparse(void);

int SqlEngine::checkCond(const int &key, const string &value, const SelCond &cond)
{
    
    switch (cond.attr) 
    {
        case 1:
            return key - atoi(cond.value);
            break;
        case 2:
            return strcmp(value.c_str(), cond.value);
            break;
    }
}

void SqlEngine::printValue(const int &key, const string &value, const int &attr)
{
    // print the tuple 
    switch (attr) {
        case 1:  // SELECT key
            fprintf(stdout, "%d\n", key);
            break;
        case 2:  // SELECT value
            fprintf(stdout, "%s\n", value.c_str());
            break;
        case 3:  // SELECT *
            fprintf(stdout, "%d '%s'\n", key, value.c_str());
            break;
    }
}

bool SqlEngine::checkValue(const string& value, const bool* comp_val, const string* vals)
{
    enum {EQVAL,LTVAL,LEVAL,GTVAL,GEVAL,ISVAL,LLVAL,GGVAL};
    
    if(!comp_val[ISVAL])
    {
        return true;
    }
    else if (comp_val[EQVAL])
    { 
        if (value.compare(vals[EQVAL]) == 0) {
            return true;
        }
        return false;
    }
    else if(comp_val[ISVAL] && value.compare(vals[LLVAL]) < 0 && value.compare(vals[GGVAL]) > 0)
    {
        return true;
    }
    else if((comp_val[LEVAL] && value.compare(vals[LLVAL]) == 0) ||
            (comp_val[GEVAL] && value.compare(vals[GGVAL]) == 0))
    {
        return true;
    }
    else
        //if all the value comparisons were false
    {
        return false;
    }
}

RC SqlEngine::run(FILE* commandline)
{
    fprintf(stdout, "Bruinbase> ");
    
    // set the command line input and start parsing user input
    sqlin = commandline;
    sqlparse();  // sqlparse() is defined in SqlParser.tab.c generated from
    // SqlParser.y by bison (bison is GNU equivalent of yacc)
    
    return 0;
}

RC SqlEngine::select(int attr, const string& table, const vector<SelCond>& cond)
{
    RecordFile rf;   // RecordFile containing the table
    RecordId   rid;  // record cursor for table scanning
    
    RC     rc;
    int    key;     
    string value;
    int    count;
    int    diff;
    
    
    
    
    //variable for index
    BTreeIndex bti;
    IndexCursor cursor;
    enum {EQKEY,LTKEY,LEKEY,GTKEY,GEKEY,ISKEY,LLKEY,GGKEY};  
    bool comp_key[6];
    for (int i = 0; i < 6; i++) {
        comp_key[i] = false;
    }
    
    //the enums are ordered so division groups like pairs
    int keys[8];
    keys[LTKEY] = INT_MAX, //greatest possible
    keys[LEKEY] = INT_MAX, //greatest possible
    keys[GTKEY] = INT_MIN,  //lowest possible
    keys[GEKEY] = INT_MIN,  //lowest possible
    keys[EQKEY] = 0;
    keys[ISKEY] = 0;  //but doesn't exist
    keys[LLKEY] = INT_MAX;
    keys[GGKEY] = INT_MIN;
    
    //ISVAL let's me know if any vals are true;
    enum {EQVAL,LTVAL,LEVAL,GTVAL,GEVAL,ISVAL,LLVAL,GGVAL};
    bool comp_val[6];
    for (int i = 0; i < 6; i++) {
        comp_key[i] = false;
    }
    
    //the enums are ordered so division groups like pairs
    string vals[8];
    vals[LTVAL] = "ZZZZ";
    vals[LEVAL] = "ZZZZ";
    vals[LLVAL] = "ZZZZ";
    vals[GTVAL] = "";
    vals[GEVAL] = "";
    vals[GGVAL] = "";
    
    
    
    
    // open the table file
    if ((rc = rf.open(table + ".tbl", 'r')) < 0) {
        fprintf(stderr, "Error: table %s does not exist\n", table.c_str());
        return rc;
    }
    
    count = 0;
    
    //default to to pick an rid, then check all constraints
    //    my method checks all constraints and chooses worthy rids
    
    //*test*/    cout << "choosing search method" << endl;
    if(bti.open(table + ".idx",'r')  == 0){
        
        // check the conditions on the tuple
        for (int i = 0; i < cond.size(); i++) {
            switch (cond[i].comp) 
            {
                case SelCond::EQ:
                    //if there is already and equality but they do not match
                    //return with an error
                    switch (cond[i].attr) 
                {
                    case 1:
                        if (comp_key[EQKEY] && checkCond(keys[EQKEY],vals[EQVAL], cond[i]) == 0)
                        {
                            rc = RC_INVALID_ATTRIBUTE;
                            goto exit_select;
                        }
                        else 
                        {
                            comp_key[EQKEY] = true;
                            keys[EQKEY] = atoi(cond[i].value);   
                        }
                        break;
                    case 2:
                        if (comp_val[EQVAL] && checkCond(keys[EQKEY],vals[EQVAL], cond[i]) == 0)
                        {
                            rc = RC_INVALID_ATTRIBUTE; goto exit_select;
                        }
                        else 
                        {
                            //*test*/ cout << "equal set true" << endl;
                            comp_val[EQVAL] = true;
                            vals[EQVAL].assign(cond[i].value);   
                        }
                        break;
                }
                    break;
                    
                case SelCond::NE:
                    //not allowed
                    rc = RC_INVALID_ATTRIBUTE; goto exit_select;
                    break;
                case SelCond::GE:
                    switch (cond[i].attr) 
                {
                    case 1:
                        if (comp_key[GEKEY])
                        {
                            if(checkCond(keys[GEKEY],vals[GEKEY], cond[i]) < 0)
                            {
                                keys[GEKEY] = atoi(cond[i].value);
                            }
                            //else keep the greatest number I have
                        }
                        //if there isn't a greater than already
                        else 
                        {
                            comp_key[GEKEY] = true;
                            keys[GEKEY] = atoi(cond[i].value);   
                        }
                        break;
                    case 2:
                        if (comp_val[GEVAL])
                        {
                            if (checkCond(keys[EQKEY],vals[EQVAL], cond[i]) < 0) {
                                vals[GEVAL].assign(cond[i].value);
                            }
                        }
                        else 
                        {
                            comp_val[GEVAL] = true;
                            vals[GEVAL].assign(cond[i].value);   
                        }
                        break;
                }
                    break;
                    
                case SelCond::LT:
                    switch (cond[i].attr) 
                {
                    case 1:
                        if (comp_key[LTKEY])
                        {
                            if(checkCond(keys[LTKEY],vals[LTVAL], cond[i]) > 0)
                            {
                                keys[LTKEY] = atoi(cond[i].value);
                            }
                            //else keep the greatest number I have
                        }
                        //if there isn't a greater than already
                        else 
                        {
                            comp_key[LTKEY] = true;
                            keys[LTKEY] = atoi(cond[i].value);   
                        }
                        break;
                    case 2:
                        if (comp_val[LTVAL])
                        {
                            if (checkCond(keys[LTKEY],vals[LTVAL], cond[i]) > 0) {
                                vals[LTVAL].assign(cond[i].value);
                            }
                        }
                        else 
                        {
                            comp_val[LTVAL] = true;
                            vals[LTVAL].assign(cond[i].value);   
                        }
                        break;
                }
                    break;
                    
                case SelCond::GT:
                    switch (cond[i].attr) 
                {
                    case 1:
                        if (comp_key[GTKEY])
                        {
                            if(checkCond(keys[GTKEY],vals[GTVAL], cond[i]) < 0)
                            {
                                keys[GTKEY] = atoi(cond[i].value);
                            }
                            //else keep the greatest number I have
                        }
                        //if there isn't a greater than already
                        else 
                        {
                            comp_key[GTKEY] = true;
                            keys[GTKEY] = atoi(cond[i].value);   
                        }
                        break;
                    case 2:
                        if (comp_val[GTVAL])
                        {
                            if (checkCond(keys[GTKEY],vals[GTVAL], cond[i]) < 0) {
                                vals[GTVAL].assign(cond[i].value);
                            }
                        }
                        else 
                        {
                            comp_val[GTVAL] = true;
                            vals[GTVAL].assign(cond[i].value);   
                        }
                        break;
                }
                    break;
                    
                case SelCond::LE:
                    switch (cond[i].attr) 
                {
                    case 1:
                        if (comp_key[LEKEY])
                        {
                            if(checkCond(keys[LEKEY],vals[LEVAL], cond[i]) > 0)
                            {
                                keys[LEKEY] = atoi(cond[i].value);
                            }
                            //else keep the greatest number I have
                        }
                        //if there isn't a greater than already
                        else 
                        {
                            comp_key[LEKEY] = true;
                            keys[LEKEY] = atoi(cond[i].value);   
                        }
                        break;
                    case 2:
                        if (comp_val[LEVAL])
                        {
                            if (checkCond(keys[LEKEY],vals[LEVAL], cond[i]) > 0) {
                                vals[LEVAL].assign(cond[i].value);
                            }
                        }
                        else 
                        {
                            comp_val[LEVAL] = true;
                            vals[LEVAL].assign(cond[i].value);   
                        }
                        break;
                }
                    break;
            }
        }
        
        //*test*/cout << "TREE: after choosing max conditions" << endl; 

        
        //choose the greatest great equality
        if (comp_key[GTKEY] || comp_key[GEKEY])
        {
            if (keys[GTKEY] >= keys[GEKEY]) 
            {
                comp_key[GEKEY] = false;
                keys[GGKEY] = keys[GTKEY];
            }
            else
            {
                comp_key[GTKEY] = false;
                keys[GGKEY] = keys[GEKEY];
            }
        }
        //choose the smallest less equality
        if (comp_key[LTKEY] || comp_key[LEKEY])
        {
            //just for fun
            //if the least lessthan key is lessthan or equal to the least less than or equal key
            if (keys[LTKEY] <= keys[LEKEY]) 
            {
                comp_key[LEKEY] = false;
                keys[LLKEY] = keys[LTKEY];
            }
            else
            {
                comp_key[LTKEY] = false;
                keys[LLKEY] = keys[LEKEY];
            }
        }
        //*test*/cout << "TREE: after choosing max condition or equal" << endl; 
        //choose the greatest great equality
        if (comp_val[GTVAL] || comp_val[GEVAL])
        {
            if (vals[GTVAL].compare(vals[GEVAL]) >= 0) 
            {
                comp_val[GEVAL] = false;
                vals[GGVAL].assign(vals[GTVAL]);
            }
            else
            {
                comp_val[GTVAL] = false;
                vals[GGVAL].assign(vals[GEVAL]);
            }
        }
         //*test*/cout << "TREE: after choosing max great val" << endl;
        //choose the smallest less equality
        if (comp_val[LTVAL] || comp_val[LEVAL])
        {
            //just for fun
            //if the least lessthan key is lessthan or equal to the least less than or equal key
            if (vals[LTVAL].compare(vals[LEVAL]) <= 0) 
            {
                comp_val[LEVAL] = false;
                vals[LLVAL].assign(vals[LTVAL]);
            }
            else
            {
                comp_val[LTVAL] = false;
                vals[LLVAL].assign(vals[LEVAL]);
            }
        }
        
        /*test 
        //cout << "TREE: after choosing max max conditions" << endl;
        if (comp_key[EQKEY])
        {
            cout <<"TREE: final equal key cond is: " << keys[EQKEY] << endl;
        }
        if (comp_key[GTKEY] || comp_key[GEKEY]) {
            cout << "TREE: final greater key cond is: " << keys[GGKEY] << endl;
        }
        if (comp_key[LTKEY] || comp_key[LEKEY]) {
            cout << "TREE: final lessest key cond is: " << keys[LLKEY] << endl;
        }
        if (comp_val[EQKEY])
        {
            cout <<"TREE: final equal val cond is: " << vals[EQKEY] << endl;
        }
        if (comp_val[GTKEY] || comp_key[GEKEY]) {
            cout << "TREE: final greater val cond is: " << vals[GGKEY] << endl;
        }
        if (comp_val[LTKEY] || comp_key[LEKEY]) {
            cout << "TREE: final lessest val cond is: " << vals[LLKEY] << endl;
        }
        //*/
        
        //now I should have equalities, min less thans,and max greater thans
        //but I must test to see if certain relations are invalid
        
        if (comp_key[EQKEY] && comp_key[GTKEY] && keys[EQKEY] <= keys[GTKEY]) {
            rc = RC_INVALID_ATTRIBUTE; goto exit_select;
        }
        if (comp_key[EQKEY] && comp_key[GEKEY] && keys[EQKEY] < keys[GEKEY]) {
            rc = RC_INVALID_ATTRIBUTE; goto exit_select;
        }
        if (comp_key[EQKEY] && comp_key[LTKEY] && keys[EQKEY] >= keys[LTKEY]) {
            rc = RC_INVALID_ATTRIBUTE; goto exit_select;
        }
        if (comp_key[EQKEY] && comp_key[LEKEY] && keys[EQKEY] > keys[LEKEY]) {
            rc = RC_INVALID_ATTRIBUTE; goto exit_select;
        }
        if (comp_key[LTKEY] && comp_key[GTKEY] && keys[LTKEY] <= keys[GTKEY])
        {
            rc = RC_INVALID_ATTRIBUTE; goto exit_select;
        }
        if (comp_key[LTKEY] && comp_key[GEKEY] && keys[LTKEY] <= keys[GEKEY])
        {
            rc = RC_INVALID_ATTRIBUTE; goto exit_select;
        }
        if (comp_key[LEKEY] && comp_key[GTKEY] && keys[LEKEY] < keys[GTKEY]) {
            rc = RC_INVALID_ATTRIBUTE; goto exit_select;
        }
        //but the final one is fine, <=# and >=# is valid
        
        if (comp_val[EQVAL] && comp_val[GTVAL] && vals[EQVAL].compare(vals[GTVAL])<=0) {
            rc = RC_INVALID_ATTRIBUTE; goto exit_select;
        }
        if (comp_val[EQVAL] && comp_val[GEVAL] && vals[EQVAL].compare(vals[GEVAL]) <0) {
            rc = RC_INVALID_ATTRIBUTE; goto exit_select;
        }
        if (comp_val[EQVAL] && comp_val[LTVAL] && vals[EQVAL].compare(vals[LTVAL]) >= 0) {
            rc = RC_INVALID_ATTRIBUTE; goto exit_select;
        }
        if (comp_val[EQVAL] && comp_val[LEVAL] && vals[EQVAL].compare(vals[LEVAL]) > 0) {
            rc = RC_INVALID_ATTRIBUTE; goto exit_select;
        }
        if (comp_val[LTVAL] && comp_val[GTVAL] && vals[LTVAL].compare(vals[GTVAL]) <= 0)
        {
            rc = RC_INVALID_ATTRIBUTE; goto exit_select;
        }
        if (comp_val[LTVAL] && comp_val[GEVAL] && vals[LTVAL].compare(vals[GEVAL]) <= 0)
        {
            rc = RC_INVALID_ATTRIBUTE; goto exit_select;
        }
        if (comp_val[LEVAL] && comp_val[GTVAL] && vals[LEVAL].compare(vals[GTVAL]) < 0) {
            rc = RC_INVALID_ATTRIBUTE; goto exit_select;
        }
        //but the final one is fine, <=# and >=# is valid
        
//////////////////
///SEARCHING and PRINTING
///////////////
        //*test
//        cout <<"here are the key bools: ";
        for (int i = 0; i < 5; i++) {
            if (comp_key[i]) {comp_key[ISKEY] = true;}
//            cout << comp_key[i];
        }
//        cout <<"\nhere are the val bools: ";
        for (int i = 0; i < 5; i++) {
            if(comp_val[i]) {comp_val[ISVAL] = true;}
//            cout << comp_val[i];
        }
//        cout << endl;
        //*/
        
        if (comp_key[EQKEY])
        {
            //*test*/ cout << "TREE: in =key printing arena" << endl;
            bti.locate(keys[EQKEY], cursor);
            bti.readForward(cursor, key, rid);
            //must also make sure < and > operations for val are satisfied
            if(attr == 2 || attr == 3)
            {   //avoid an unnecessary page read
                rf.read(rid, key, value);
            }
            if (checkValue(value, comp_val, vals))
            {
                printValue(key, value, attr);
                count++;
            }
        }    
        else if (comp_key[GEKEY] || comp_key[GTKEY])
        {
            //*test*/ cout << "TREE: in >key printing arena" << endl;
            //if there are greater than
            //and if they also have less thans
            //also ifs for value range
            
            if(bti.locate(keys[GGKEY],cursor) != RC_END_OF_TREE){
            //if there is an upper bound from the less than comparator
            if(comp_key[LEKEY] || comp_key[LTKEY])
            {
                while(bti.readForward(cursor,key,rid)== 0 && key < keys[LLKEY])
                {
                    if (comp_key[GTKEY] && key == keys[GGKEY]) {
                        //skip the first entry that because it is equal to
                        continue;
                    }
                    if (attr == 2 || attr == 3 || comp_val[ISVAL]) {
                        rf.read(rid, key, value);
                    }
                    if (checkValue(value, comp_val, vals))
                    {
                        printValue(key, value, attr);
                        count++;
                    }
                }
                if(comp_key[LEKEY] && keys[LLKEY] == key)
                {
                    if(attr == 2 && attr == 3 || comp_val[ISVAL]){
                        rf.read(rid,key, value);
                    }
                    if (checkValue(value, comp_val, vals))
                    {
                        printValue(key, value, attr);
                        count++;
                    }
                }
            }
                //if there are no upper bounds
            else
            {
                while(bti.readForward(cursor,key,rid) == 0)
                {
                    if (comp_key[GTKEY] && key == keys[GGKEY]) {
                        //skip the first entry that because it is equal to
                        continue;
                    }
                    if (attr == 2 || attr == 3) {
                        rf.read(rid, key, value);
                    }
                    if (checkValue(value, comp_val, vals))
                    {
                        printValue(key, value, attr);
                        count++;
                    }
                }
            }
            }
        }
        else if (comp_key[LEKEY] || comp_key[LTKEY])
        {
           //*test*/ cout << "TREE: in <key printing arena" << endl;
            //else there are only less than key comparators
            //make sure to check for values aswell
            cursor.pid = 1; cursor.eid = 0;
            while (bti.readForward(cursor, key, rid) == 0 && key < keys[LLKEY] )
            {
                if (attr == 2 || attr == 3 || comp_val[ISVAL]) {
                    rf.read(rid, key, value);
                }
                if (checkValue(value, comp_val, vals))
                {
                    printValue(key, value, attr);
                    count++;
                }
            }
            if (comp_key[LEKEY] && key == keys[LLKEY]) {
                if (attr == 2 || attr == 3 || comp_val[ISVAL]) {
                    rf.read(rid, key, value);
                }
                if (checkValue(value, comp_val, vals))
                {
                    printValue(key, value, attr);
                    count++;
                }
            }   
        }
        //if there are no key comparisons 
        else if(!comp_key[ISKEY])
        {
            cursor.pid = 1; cursor.eid = 0;
            while(bti.readForward(cursor, key, rid) == 0)
            {
                if (attr == 2 || attr == 3 || comp_val[ISVAL]) {
                    rf.read(rid, key, value);
                }
                if (checkValue(value, comp_val, vals))
                {
                    printValue(key, value, attr);
                    count++;
                }
            } 
        }  
    }
    
    else 
    {
        // scan the table file from the beginning
        rid.pid = rid.sid = 0;
        
        while (rid < rf.endRid()) {
            // read the tuple
            if ((rc = rf.read(rid, key, value)) < 0) {
                fprintf(stderr, "Error: while reading a tuple from table %s\n", table.c_str());
                goto exit_select;
            }
            
            // check the conditions on the tuple
            for (unsigned i = 0; i < cond.size(); i++) {
                // compute the difference between the tuple value and the condition value
                
                diff = checkCond(key, value, cond[i]);
                
                // skip the tuple if any condition is not met
                switch (cond[i].comp) {
                    case SelCond::EQ:
                        if (diff != 0) goto next_tuple;
                        break;
                    case SelCond::NE:
                        if (diff == 0) goto next_tuple;
                        break;
                    case SelCond::GT:
                        if (diff <= 0) goto next_tuple;
                        break;
                    case SelCond::LT:
                        if (diff >= 0) goto next_tuple;
                        break;
                    case SelCond::GE:
                        if (diff < 0) goto next_tuple;
                        break;
                    case SelCond::LE:
                        if (diff > 0) goto next_tuple;
                        break;
                }
            }
            
            // the condition is met for the tuple. 
            // increase matching tuple counter
            count++;
            
            // print the tuple 
            printValue(key, value, attr);
            
            // move to the next tuple
        next_tuple:
            ++rid;
        }
        
        
    }
    // print matching tuple count if "select count(*)"
    if (attr == 4) {
        fprintf(stdout, "%d\n", count);
    }
    rc = 0;
    // close the table file and return
exit_select:
    rf.close();
    return rc;
}

RC SqlEngine::load(const string& table, const string& loadfile, bool index)
{
    RC rc;
	//dealing with two files
	
	//1st is source named as "datafile"
	//open datafile with fopen
	char * lf = (char*)loadfile.c_str();
	FILE * datafile;
	datafile = fopen(lf, "r");
    //    datafile = fopen("movie.del","r");
	
	//2nd is the table named as record
	//open record with RecordFile::open()
	string loadfile_tbl = table +".tbl";
    remove((char*)loadfile_tbl.c_str()); //******added
	RecordFile record;
	if((rc = record.open(loadfile_tbl,'w')) < 0){return rc;}
	
    
    BTreeIndex bti;
    if (index == true) 
    {
        std::string indexfile = table + ".idx";
        remove((char*)indexfile.c_str());
        if((rc = bti.open(indexfile, 'w')) < 0){return rc;}
    }
    
    
    
	//variables
	char cline[100];
	int key = -1;
    std::string line, title;
	
	//read key and title from datafile
	//data stored as
	//				:key,"title"
    
	while(fgets(cline, 100, datafile) != NULL){
        
        
		/*
         //key #
         for(iter = 0; iter < 100; iter++){
         if(line[iter] == ','){
         iter++;
         break;
         }
         strid[iter] = line[iter];
         }
         strid[iter-1] = '\0';
         //end key#
         
         //title
         for(iter2 = 0; iter < 100; iter++){
         if(line[iter] == '\0'){
         iter++;
         break;
         }
         else if(line[iter] == '\"'){
         continue;
         }
         else title[iter2] = line[iter];
         iter2++;
         }
         title[iter2] = '\0';
         std::string title_string(title);
         //end title
         */
        line.assign(cline);
        if((rc = parseLoadLine(line, key, title)) < 0){return rc;}
		//write to table
		RecordId recordspot = record.endRid();
		if((rc = record.append(key, title, recordspot)) < 0){return rc;}
        if(index == true)
        {
            if((rc = bti.insert(key, recordspot)) < 0){return rc;}
        }
	}
    if (index == true) {
        if((rc = bti.close()) < 0){return rc;}
    }
    
	//close record with RecordFile::close()
	if((rc = record.close()) < 0){return rc;}
	//close datafile with fclose
	fclose(datafile);
	return 0;
}

RC SqlEngine::parseLoadLine(const string& line, int& key, string& value)
{
    const char *s;
    char        c;
    string::size_type loc;
    
    // ignore beginning white spaces
    c = *(s = line.c_str());
    while (c == ' ' || c == '\t') { c = *++s; }
    
    // get the integer key value
    key = atoi(s);
    
    // look for comma
    s = strchr(s, ',');
    if (s == NULL) { return RC_INVALID_FILE_FORMAT; }
    
    // ignore white spaces
    do { c = *++s; } while (c == ' ' || c == '\t');
    
    // if there is nothing left, set the value to empty string
    if (c == 0) { 
        value.erase();
        return 0;
    }
    
    // is the value field delimited by ' or "?
    if (c == '\'' || c == '"') {
        s++;
    } else {
        c = '\n';
    }
    
    // get the value string
    value.assign(s);
    loc = value.find(c, 0);
    if (loc != string::npos) { value.erase(loc); }
    
    return 0;
}
